﻿using System;
using System.Collections;
using System.Data;
using Telerik.Web.UI;
using System.Web.UI;
using KPIS.GERP.GAMS.DAL;
using KPIS.GERP.GAMS.APP.App_UI.Commons;
using System.Collections.Generic;

namespace KPIS.GERP.GAMS.APP.App_UI.Forms
{
    public partial class Reports : System.Web.UI.Page
    {
        Commons.Main main = new Commons.Main();
        D_ACM_MAS_FISCAL_Period d_acm_fiscal_period = new D_ACM_MAS_FISCAL_Period();
        D_BGM_MAS_Source source = new D_BGM_MAS_Source();
        D_ACM_GENERAL_Ledger lg = new D_ACM_GENERAL_Ledger();
        MailMerge mailMerge = new MailMerge();
        
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                hReportName.Text = Request.QueryString["ReportName"];
                if (hReportName.Text == "TrialBalance_Fiscal")
                {
                    dategroup.Visible = false;
                }
                else if ((hReportName.Text == "Inventory") || (hReportName.Text == "Expense"))
                {
                    dategroup.Visible = false;
                }
                else
                {
                    dategroup.Visible = true;
                }

                switch (hReportName.Text)
                {
                    case "Balance_Sheet":
                        btnReport.Visible = false;
                        btnReport2.Visible = true;

                        chkPrevReport.Visible = true;
                        tblFilterYear.Visible = false;
                        tblFilterAccount.Visible = false;
                        chkPrevReport_CheckedChanged(null, null);
                        btnReportExcel.Visible = false;
                        break;
                    case "Income_Statement":
                        btnReport.Visible = false;
                        btnReport2.Visible = true;

                        txtBgmYear.Text = source.GetCurrentBgmYear();

                        chkPrevReport.Visible = true;
                        tblFilterYear.Visible = true;
                        tblFilterAccount.Visible = false;
                        tblFilterQuarter.Visible = false;
                        tblFilterDate.Visible = false;
                        btnReportExcel.Visible = false;
                        break;
                    case "Ledgers":
                        btnReport.Visible = true;
                        btnReport2.Visible = false;

                        chkPrevReport.Visible = false;
                        tblFilterYear.Visible = false;
                        tblFilterAccount.Visible = true;
                        tblFilterQuarter.Visible = false;
                        tblFilterDate.Visible = true;
                        LoadDDLAccountSeq();
                        btnReportExcel.Visible = true;
                        break;
                    default:
                        btnReport.Visible = true;
                        btnReport2.Visible = false;

                        chkPrevReport.Visible = false;
                        tblFilterAccount.Visible = false;
                        tblFilterYear.Visible = false;
                        tblFilterQuarter.Visible = false;
                        tblFilterDate.Visible = true;
                        btnReportExcel.Visible = false;
                        break;
                }

                LoadGrdBgmSource();
            }
        }

        protected void LoadGrdBgmSource()
        {
            grdBgmSource.DataSource = source.GetList();
            grdBgmSource.DataBind();
        }

        protected void RadAjaxManager1_AjaxRequest(object sender, AjaxRequestEventArgs e)
        {

        }
        protected void  rbChooseAll_OnCheckedChanged(object sender, EventArgs e)
        {
        }
        protected void  rbChooseBookGeneral_OnCheckedChanged(object sender, EventArgs e)
        {
        }
        protected void  rbChooseBookReceive_OnCheckedChanged(object sender, EventArgs e)
        {
        }
        protected void  rbChooseBookPayment_OnCheckedChanged(object sender, EventArgs e)
        {
        }
        protected void rbChooseBookAdjust_OnCheckedChanged(object sender, EventArgs e)
        {
        }

        protected void btnReport_Click(object sender, EventArgs e)
        {
            if ((hReportName.Text != "TrialBalance_Fiscal") && (hReportName.Text != "Inventory") && (hReportName.Text != "Expense"))
            {
                if (dtpStartDate.GetDateValue() != null && dtpEndDate.GetDateValue() != null)
                {
                    string strBgmSourceSeq = "";

                    if (grdBgmSource.SelectedItems.Count == 0)
                    {
                        return;
                    }
                    else
                    {
                        for (int i = 0; i < grdBgmSource.SelectedItems.Count; i++)
                        {
                            GridDataItem item = (GridDataItem)grdBgmSource.SelectedItems[i];
                            strBgmSourceSeq += item["bgm_source_seq"].Text + (i == grdBgmSource.SelectedItems.Count - 1 ? "" : ",");
                        }
                    }

                    Session["sSourceSeq"] = strBgmSourceSeq;
                    Session["sStartDate"] = (dtpStartDate.GetDateValue() != null ? dtpStartDate.GetDateValue().Value.ToLongDateString() : null);
                    Session["sEndDate"] = (dtpEndDate.GetDateValue() != null ? dtpEndDate.GetDateValue().Value.ToLongDateString() : null);
                    Session["dStartDate"] = dtpStartDate.GetDateValue();
                    Session["dEndDate"] = dtpEndDate.GetDateValue();
                }
                else
                {
                    main.MessageAlert(Page, "กรุณาระบุวันที่ก่อนออกรายงาน");
                    return;
                }
            }

            if (hReportName.Text == "Ledgers")
            {
                Session["AccountSeq"] = ddlAccountSeq.SelectedValue;
                Session["IsExcel"] = false;
            }
            Session["sReportName"] = hReportName.Text;
            ScriptManager.RegisterClientScriptBlock(Page, this.GetType(), "", "openWin()", true);
        }

        protected void btnReportExcel_Click(object sender, EventArgs e)
        {
            if ((hReportName.Text != "TrialBalance_Fiscal") && (hReportName.Text != "Inventory") && (hReportName.Text != "Expense"))
            {
                if (dtpStartDate.GetDateValue() != null && dtpEndDate.GetDateValue() != null)
                {
                    string strBgmSourceSeq = "";

                    if (grdBgmSource.SelectedItems.Count == 0)
                    {
                        return;
                    }
                    else
                    {
                        for (int i = 0; i < grdBgmSource.SelectedItems.Count; i++)
                        {
                            GridDataItem item = (GridDataItem)grdBgmSource.SelectedItems[i];
                            strBgmSourceSeq += item["bgm_source_seq"].Text + (i == grdBgmSource.SelectedItems.Count - 1 ? "" : ",");
                        }
                    }

                    Session["sSourceSeq"] = strBgmSourceSeq;
                    Session["sStartDate"] = (dtpStartDate.GetDateValue() != null ? dtpStartDate.GetDateValue().Value.ToLongDateString() : null);
                    Session["sEndDate"] = (dtpEndDate.GetDateValue() != null ? dtpEndDate.GetDateValue().Value.ToLongDateString() : null);
                    Session["dStartDate"] = dtpStartDate.GetDateValue();
                    Session["dEndDate"] = dtpEndDate.GetDateValue();
                }
                else
                {
                    main.MessageAlert(Page, "กรุณาระบุวันที่ก่อนออกรายงาน");
                    return;
                }
            }

            if (hReportName.Text == "Ledgers")
            {
                Session["AccountSeq"] = ddlAccountSeq.SelectedValue;
                Session["IsExcel"] = true;
            }

            Session["sReportName"] = hReportName.Text;
            ScriptManager.RegisterClientScriptBlock(Page, this.GetType(), "", "openWin()", true);
        }

        protected void LoadDDLAccountSeq()
        {
            ddlAccountSeq.DataSource = new D_ACM_MAS_Account().GetListForLookUp();
            ddlAccountSeq.DataBind();

            main.InsertEmptyDDLAll(ddlAccountSeq);
        }

        protected void btnReport2_Click(object sender, EventArgs e)
        {
            string strBgmSourceSeq = "";
            DateTime? dtStartDate = dtpStartDate.GetDateValue();
            DateTime? dtEndDate = dtpEndDate.GetDateValue();

            if (grdBgmSource.SelectedItems.Count == 0)
            {
                return;
            }
            else
            {
                for (int i = 0; i < grdBgmSource.SelectedItems.Count; i++)
                {
                    GridDataItem item = (GridDataItem)grdBgmSource.SelectedItems[i];
                    strBgmSourceSeq += item["bgm_source_seq"].Text + (i == grdBgmSource.SelectedItems.Count - 1 ? "" : ",");
                }
            }

            switch (hReportName.Text)
            {
                case "Balance_Sheet":
                    BalanceSheet(strBgmSourceSeq, dtStartDate, dtEndDate);
                    break;
                case "Income_Statement":
                    if (txtBgmYear.Text.Length == 0)
                    {
                        return;
                    }

                    IncomeStatement(strBgmSourceSeq, txtBgmYear.Text);
                    break;
            }
        }

        private Boolean ValidateCheck(string pFormName)
        {
            Boolean pFlag=true ;
            return pFlag;
        }

        protected void chkPrevReport_CheckedChanged(object sender, EventArgs e)
        {
            tblFilterDate.Visible = !chkPrevReport.Checked && hReportName.Text == "Balance_Sheet";
            tblFilterQuarter.Visible = chkPrevReport.Checked;
            tblFilterYear.Visible = !chkPrevReport.Checked && hReportName.Text == "Income_Statement";
        }

        #region report
        private void BalanceSheet(string strBgmSourceSeq, DateTime? dtStartDate, DateTime? dtEndDate)
        {
            DataTable BalanceSheetH = lg.ReportBalanceSheetHeader("'1', '2', '3'");
            Dictionary<string, string> dic = new Dictionary<string, string>();

            int nRow = 3;
            double dSumMain1 = 0;
            double dSumSub1 = 0;
            double dSumMain2 = 0;
            double dSumSub2 = 0;
            double dSumMain3 = 0;
            double dSumSub3 = 0;
            double dSum21 = 0;
            double dSum22 = 0;
            double dSum23 = 0;
            int nBgmYear = Convert.ToInt32(source.GetCurrentBgmYear()) - 543;
            int nSheet = 1;

            if (chkPrevReport.Checked)
            {
                switch (rblQuarter.SelectedValue)
                {
                    case "1":
                        dtStartDate = new DateTime(nBgmYear - 1, 10, 1);
                        dtEndDate = new DateTime(nBgmYear - 1, 12, 31);
                        break;
                    case "2":
                        dtStartDate = new DateTime(nBgmYear, 1, 1);
                        dtEndDate = new DateTime(nBgmYear, 3, 31);
                        break;
                    case "3":
                        dtStartDate = new DateTime(nBgmYear, 4, 1);
                        dtEndDate = new DateTime(nBgmYear, 6, 30);
                        break;
                    case "4":
                        dtStartDate = new DateTime(nBgmYear, 7, 1);
                        dtEndDate = new DateTime(nBgmYear, 9, 30);
                        break;
                    default:
                        dtStartDate = new DateTime(nBgmYear - 1, 10, 1);
                        dtEndDate = new DateTime(nBgmYear, 9, 30);
                        break;
                }
            }

            dic.Add("A1", "ณ วันที่ " + (dtEndDate.HasValue ? dtEndDate.Value.ToLongDateString() : ""));
            if (chkPrevReport.Checked)
            {
                dic.Add("D2", rblQuarter.SelectedItem.Text + " " + nBgmYear.ToString());
                dic.Add("E2", rblQuarter.SelectedItem.Text + " " + (nBgmYear - 1).ToString());
                dic.Add("F2", rblQuarter.SelectedItem.Text + " " + (nBgmYear - 2).ToString());
            }
            else
            {
                dtStartDate = new DateTime(nBgmYear - 1, 10, 1);
                dtEndDate = new DateTime(nBgmYear, 9, 30);
                dic.Add("D2", "หมายเหตุ");
            }

            for (int i = 0; i < BalanceSheetH.Rows.Count; i++)
            {
                DataRow dr = BalanceSheetH.Rows[i];
                string strAccountSeqMain = dr["account_seq_main"].ToString();
                string strAccountSeqSub = dr["account_seq_sub"].ToString();
                string strAccountCodeMain = dr["account_code_main"].ToString();
                string strAccountCodeSub = dr["account_code_sub"].ToString();
                string strAccountNameMain = dr["account_name_main"].ToString();
                string strAccountNameSub = dr["account_name_sub"].ToString();

                if (i == 0)
                {
                    dic.Add("A" + nRow.ToString(), strAccountNameMain);
                    nRow++;
                    dic.Add("B" + nRow.ToString(), strAccountNameSub);
                    nRow++;
                }

                DataTable BalanceSheetS = null;

                if (chkPrevReport.Checked)
                {
                    BalanceSheetS = lg.ReportBalanceSheetDetailsPrev(strBgmSourceSeq, dtStartDate.Value, dtEndDate.Value, strAccountSeqSub, chkPost.Checked);
                }
                else
                {
                    BalanceSheetS = lg.ReportBalanceSheetDetails(strBgmSourceSeq, null, dtStartDate, dtEndDate, strAccountSeqSub, chkPost.Checked);
                }

                foreach (DataRow drSub in BalanceSheetS.Rows)
                {
                    dic.Add("C" + nRow.ToString(), drSub["account_name"].ToString());

                    if (chkPrevReport.Checked)
                    {
                        dic.Add("D" + nRow.ToString(), (main.ConvertNullDouble(drSub["amount"]) ?? 0).ToString("#,##0.00"));
                        dic.Add("E" + nRow.ToString(), (main.ConvertNullDouble(drSub["amount2"]) ?? 0).ToString("#,##0.00"));
                        dic.Add("F" + nRow.ToString(), (main.ConvertNullDouble(drSub["amount3"]) ?? 0).ToString("#,##0.00"));
                        dSumMain2 += main.ConvertNullDouble(drSub["amount2"]) ?? 0;
                        dSumSub2 += main.ConvertNullDouble(drSub["amount2"]) ?? 0;
                        dSumMain3 += main.ConvertNullDouble(drSub["amount3"]) ?? 0;
                        dSumSub3 += main.ConvertNullDouble(drSub["amount3"]) ?? 0;
                    }
                    else
                    {
                        dic.Add("D" + nRow.ToString(), (nSheet).ToString());
                        dic.Add("E" + nRow.ToString(), (main.ConvertNullDouble(drSub["amount"]) ?? 0).ToString("#,##0.00"));
                    }

                    dSumMain1 += main.ConvertNullDouble(drSub["amount"]) ?? 0;
                    dSumSub1 += main.ConvertNullDouble(drSub["amount"]) ?? 0;

                    if (dr["account_group"].ToString() == "1")
                    {
                    }
                    else
                    {
                        dSum21 += main.ConvertNullDouble(drSub["amount"]) ?? 0;
                        if (chkPrevReport.Checked)
                        {
                            dSum22 += main.ConvertNullDouble(drSub["amount2"]) ?? 0;
                            dSum23 += main.ConvertNullDouble(drSub["amount3"]) ?? 0;
                        }
                    }

                    nRow++;

                    // add detail sheet
                    var detail = lg.ReportIncomeStatments(strBgmSourceSeq, dtStartDate.Value, dtEndDate.Value
                        , null, drSub["account_seq"].ToString(), chkPost.Checked);
                    int nRowDtls = 2;

                    dic.Add(nSheet.ToString() + "!A1", "รหัสบัญชี");
                    dic.Add(nSheet.ToString() + "!B1", "รหัส GFMIS");
                    dic.Add(nSheet.ToString() + "!C1", "ชื่อบัญชี");
                    dic.Add(nSheet.ToString() + "!D1", "บาท");

                    foreach (DataRow drDetail in detail.Rows)
                    {
                        dic.Add(nSheet.ToString() + "!A" + nRowDtls.ToString(), drDetail["account_code"].ToString());
                        dic.Add(nSheet.ToString() + "!B" + nRowDtls.ToString(), drDetail["gfmis_code"].ToString());
                        dic.Add(nSheet.ToString() + "!C" + nRowDtls.ToString(), drDetail["account_name"].ToString());
                        dic.Add(nSheet.ToString() + "!D" + nRowDtls.ToString(), drDetail["amount"].ToString());
                        nRowDtls++;
                    }

                    nSheet++;
                }

                dic.Add("B" + nRow.ToString(), "รวม" + strAccountNameSub);

                if (chkPrevReport.Checked)
                {
                    dic.Add("D" + nRow.ToString(), dSumSub1.ToString("#,##0.00"));
                    dic.Add("E" + nRow.ToString(), dSumSub2.ToString("#,##0.00"));
                    dic.Add("F" + nRow.ToString(), dSumSub3.ToString("#,##0.00"));
                    dSumSub2 = 0;
                    dSumSub3 = 0;
                }
                else
                {
                    dic.Add("E" + nRow.ToString(), dSumSub1.ToString("#,##0.00"));
                }
                dSumSub1 = 0;
                nRow++;

                if (i == BalanceSheetH.Rows.Count - 1 || BalanceSheetH.Rows[i + 1]["account_seq_main"].ToString() != strAccountSeqMain)
                {
                    dic.Add("A" + nRow.ToString(), "รวม" + strAccountNameMain);


                    if (chkPrevReport.Checked)
                    {
                        dic.Add("D" + nRow.ToString(), dSumMain1.ToString("#,##0.00"));
                        dic.Add("E" + nRow.ToString(), dSumMain2.ToString("#,##0.00"));
                        dic.Add("F" + nRow.ToString(), dSumMain3.ToString("#,##0.00"));
                        dSumMain2 = 0;
                        dSumMain3 = 0;
                    }
                    else
                    {
                        dic.Add("E" + nRow.ToString(), dSumMain1.ToString("#,##0.00"));
                    }

                    dSumMain1 = 0;
                    nRow++;
                    nRow++;

                    if (i != BalanceSheetH.Rows.Count - 1)
                    {
                        dic.Add("A" + nRow.ToString(), BalanceSheetH.Rows[i + 1]["account_name_main"].ToString());
                        nRow++;
                        dic.Add("B" + nRow.ToString(), BalanceSheetH.Rows[i + 1]["account_name_sub"].ToString());
                        nRow++;
                    }
                    else
                    {
                        dic.Add("A" + nRow.ToString(), "รวมหนี้สินและส่วนของทุน");

                        if (chkPrevReport.Checked)
                        {
                            dic.Add("D" + nRow.ToString(), dSum21.ToString("#,##0.00"));
                            dic.Add("E" + nRow.ToString(), dSum22.ToString("#,##0.00"));
                            dic.Add("F" + nRow.ToString(), dSum23.ToString("#,##0.00"));
                        }
                        else
                        {
                            dic.Add("E" + nRow.ToString(), dSum21.ToString("#,##0.00"));
                        }
                    }
                }
                else if (i != BalanceSheetH.Rows.Count - 1)
                {
                    dic.Add("B" + nRow.ToString(), BalanceSheetH.Rows[i + 1]["account_name_sub"].ToString());
                    nRow++;
                }
            }

            if (chkPrevReport.Checked)
            {
                mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/งบดุลเปรียบเทียบ.xlsx"), string.Format("งบดุลเปรียบเทียบ-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), null, dic);
            }
            else
            {
                mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/งบดุล.xlsx"), string.Format("งบดุล-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), null, dic);
            }
        }

        private void IncomeStatement(string strBgmSourceSeq, string strBgmYear)
        {
            DataTable IncomeStatementH = lg.ReportBalanceSheetHeader("'4', '5'");
            Dictionary<string, string> dic = new Dictionary<string, string>();

            int nRow = 3;
            double dSumMain1 = 0;
            double dSumSub1 = 0;
            double dSumMain2 = 0;
            double dSumSub2 = 0;
            double dSumMain3 = 0;
            double dSumSub3 = 0;
            double dSumIncome1 = 0;
            double dSumOutcome1 = 0;
            double dSumIncome2 = 0;
            double dSumOutcome2 = 0;
            double dSumIncome3 = 0;
            double dSumOutcome3 = 0;
            int nBgmYear = Convert.ToInt32(source.GetCurrentBgmYear()) - 543;
            DateTime date = new DateTime(nBgmYear, 9, 30);
            DateTime? dtStartDate = null;
            DateTime? dtEndDate = null;
            int nSheet = 1;

            if (chkPrevReport.Checked)
            {
                switch (rblQuarter.SelectedValue)
                {
                    case "1":
                        dtStartDate = new DateTime(nBgmYear - 1, 10, 1);
                        dtEndDate = new DateTime(nBgmYear - 1, 12, 31);
                        break;
                    case "2":
                        dtStartDate = new DateTime(nBgmYear, 1, 1);
                        dtEndDate = new DateTime(nBgmYear, 3, 31);
                        break;
                    case "3":
                        dtStartDate = new DateTime(nBgmYear, 4, 1);
                        dtEndDate = new DateTime(nBgmYear, 6, 30);
                        break;
                    case "4":
                        dtStartDate = new DateTime(nBgmYear, 7, 1);
                        dtEndDate = new DateTime(nBgmYear, 9, 30);
                        break;
                    default:
                        dtStartDate = new DateTime(nBgmYear - 1, 10, 1);
                        dtEndDate = new DateTime(nBgmYear, 9, 30);
                        break;
                }
            }

            dic.Add("A1", "สำหรับปีสิ้นสุด วันที่ " + date.ToLongDateString());
            if (chkPrevReport.Checked)
            {
                dic.Add("D2", rblQuarter.SelectedItem.Text + " " + (nBgmYear + 543).ToString());
                dic.Add("E2", rblQuarter.SelectedItem.Text + " " + (nBgmYear + 543 - 1).ToString());
                dic.Add("F2", rblQuarter.SelectedItem.Text + " " + (nBgmYear + 543 - 2).ToString());
            }
            else
            {
                dic.Add("D2", "หมายเหตุ");
            }

            for (int i = 0; i < IncomeStatementH.Rows.Count; i++)
            {
                DataRow dr = IncomeStatementH.Rows[i];
                string strAccountSeqMain = dr["account_seq_main"].ToString();
                string strAccountSeqSub = dr["account_seq_sub"].ToString();
                string strAccountCodeMain = dr["account_code_main"].ToString();
                string strAccountCodeSub = dr["account_code_sub"].ToString();
                string strAccountNameMain = dr["account_name_main"].ToString();
                string strAccountNameSub = dr["account_name_sub"].ToString();

                if (i == 0)
                {
                    dic.Add("A" + nRow.ToString(), strAccountNameMain);
                    nRow++;
                    dic.Add("B" + nRow.ToString(), strAccountNameSub);
                    nRow++;
                }

                DataTable IncomeStatementS = null;

                if (chkPrevReport.Checked)
                {
                    IncomeStatementS = lg.ReportBalanceSheetDetailsPrev(strBgmSourceSeq, dtStartDate.Value, dtEndDate.Value
                        , strAccountSeqSub, chkPost.Checked);
                }
                else
                {
                    dtStartDate = new DateTime(nBgmYear - 1, 10, 1);
                    dtEndDate = new DateTime(nBgmYear, 9, 30);
                    IncomeStatementS = lg.ReportBalanceSheetDetails(strBgmSourceSeq, null, dtStartDate, dtEndDate
                        , strAccountSeqSub, chkPost.Checked);
                }

                
                foreach (DataRow drSub in IncomeStatementS.Rows)
                {
                    dic.Add("C" + nRow.ToString(), drSub["account_name"].ToString());

                    if (chkPrevReport.Checked)
                    {
                        dic.Add("D" + nRow.ToString(), (main.ConvertNullDouble(drSub["amount"]) ?? 0).ToString("#,##0.00"));
                        dic.Add("E" + nRow.ToString(), (main.ConvertNullDouble(drSub["amount2"]) ?? 0).ToString("#,##0.00"));
                        dic.Add("F" + nRow.ToString(), (main.ConvertNullDouble(drSub["amount3"]) ?? 0).ToString("#,##0.00"));
                        dSumMain2 += main.ConvertNullDouble(drSub["amount2"]) ?? 0;
                        dSumSub2 += main.ConvertNullDouble(drSub["amount2"]) ?? 0;
                        dSumMain3 += main.ConvertNullDouble(drSub["amount3"]) ?? 0;
                        dSumSub3 += main.ConvertNullDouble(drSub["amount3"]) ?? 0;
                    }
                    else
                    {
                        dic.Add("D" + nRow.ToString(), nSheet.ToString());
                        dic.Add("E" + nRow.ToString(), (main.ConvertNullDouble(drSub["amount"]) ?? 0).ToString("#,##0.00"));
                    }

                    dSumMain1 += main.ConvertNullDouble(drSub["amount"]) ?? 0;
                    dSumSub1 += main.ConvertNullDouble(drSub["amount"]) ?? 0;

                    if (dr["account_group"].ToString() == "4")
                    {
                        dSumIncome1 = dSumMain1;
                        if (chkPrevReport.Checked)
                        {
                            dSumIncome2 = dSumMain2;
                            dSumIncome3 = dSumMain3;
                        }
                    }
                    else
                    {
                        dSumOutcome1 = dSumMain1;
                        if (chkPrevReport.Checked)
                        {
                            dSumOutcome2 = dSumMain2;
                            dSumOutcome3 = dSumMain3;
                        }
                    }
                    
                    nRow++;

                    // add detail sheet
                    var detail = lg.ReportIncomeStatments(strBgmSourceSeq, dtStartDate.Value, dtEndDate.Value
                        , null, drSub["account_seq"].ToString(), chkPost.Checked);
                    int nRowDtls = 2;

                    dic.Add(nSheet.ToString() + "!A1", "รหัสบัญชี");
                    dic.Add(nSheet.ToString() + "!B1", "รหัส GFMIS");
                    dic.Add(nSheet.ToString() + "!C1", "ชื่อบัญชี");
                    dic.Add(nSheet.ToString() + "!D1", "บาท");

                    foreach (DataRow drDetail in detail.Rows)
                    {
                        dic.Add(nSheet.ToString() + "!A" + nRowDtls.ToString(), drDetail["account_code"].ToString());
                        dic.Add(nSheet.ToString() + "!B" + nRowDtls.ToString(), drDetail["gfmis_code"].ToString());
                        dic.Add(nSheet.ToString() + "!C" + nRowDtls.ToString(), drDetail["account_name"].ToString());
                        dic.Add(nSheet.ToString() + "!D" + nRowDtls.ToString(), drDetail["amount"].ToString());
                        nRowDtls++;
                    }

                    nSheet++;
                }

                dic.Add("B" + nRow.ToString(), "รวม" + strAccountNameSub);

                if (chkPrevReport.Checked)
                {
                    dic.Add("D" + nRow.ToString(), dSumSub1.ToString("#,##0.00"));
                    dic.Add("E" + nRow.ToString(), dSumSub2.ToString("#,##0.00"));
                    dic.Add("F" + nRow.ToString(), dSumSub3.ToString("#,##0.00"));
                }
                else
                {
                    dic.Add("E" + nRow.ToString(), dSumSub1.ToString("#,##0.00"));
                }

                dSumSub1 = 0;
                nRow++;

                if (i == IncomeStatementH.Rows.Count - 1 || IncomeStatementH.Rows[i + 1]["account_seq_main"].ToString() != strAccountSeqMain)
                {
                    dic.Add("A" + nRow.ToString(), "รวม" + strAccountNameMain);

                    if (chkPrevReport.Checked)
                    {
                        dic.Add("D" + nRow.ToString(), dSumMain1.ToString("#,##0.00"));
                        dic.Add("E" + nRow.ToString(), dSumMain2.ToString("#,##0.00"));
                        dic.Add("F" + nRow.ToString(), dSumMain3.ToString("#,##0.00"));
                        dSumMain2 = 0;
                        dSumMain3 = 0;
                    }
                    else
                    {
                        dic.Add("E" + nRow.ToString(), dSumMain1.ToString("#,##0.00"));
                    }

                    dSumMain1 = 0;
                    nRow++;
                    nRow++;

                    if (i != IncomeStatementH.Rows.Count - 1)
                    {
                        dic.Add("A" + nRow.ToString(), IncomeStatementH.Rows[i + 1]["account_name_main"].ToString());
                        nRow++;
                        dic.Add("B" + nRow.ToString(), IncomeStatementH.Rows[i + 1]["account_name_sub"].ToString());
                        nRow++;
                    }
                    else
                    {
                        dic.Add("A" + nRow.ToString(), "รายได้สูง(ต่ำ)กว่าค่าใช้จ่าย");
                        var dSum1 = dSumIncome1 - dSumOutcome1;

                        if (chkPrevReport.Checked)
                        {
                            var dSum2 = dSumIncome2 - dSumOutcome2;
                            var dSum3 = dSumIncome3 - dSumOutcome3;

                            dic.Add("D" + nRow.ToString(), dSum1 >= 0 ? dSum1.ToString("#,##0.00") : (-dSum1).ToString("(#,##0.00)"));
                            dic.Add("E" + nRow.ToString(), dSum2 >= 0 ? dSum2.ToString("#,##0.00") : (-dSum2).ToString("(#,##0.00)"));
                            dic.Add("F" + nRow.ToString(), dSum3 >= 0 ? dSum3.ToString("#,##0.00") : (-dSum3).ToString("(#,##0.00)"));
                        }
                        else
                        {
                            dic.Add("E" + nRow.ToString(), dSum1 >= 0 ? dSum1.ToString("#,##0.00") : (- dSum1).ToString("(#,##0.00)"));
                        }
                    }
                }
                else if (i != IncomeStatementH.Rows.Count - 1)
                {
                    dic.Add("B" + nRow.ToString(), IncomeStatementH.Rows[i + 1]["account_name_sub"].ToString());
                    nRow++;
                }
            }

            if (chkPrevReport.Checked)
            {
                mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/งบรายได้และค่าใช้จ่ายเปรียบเทียบ.xlsx"), string.Format("งบรายได้และค่าใช้จ่ายเปรียบเทียบ-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), null, dic);
            }
            else
            {
                mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/งบรายได้และค่าใช้จ่าย.xlsx"), string.Format("งบรายได้และค่าใช้จ่าย-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), null, dic);
            }
        }
        #endregion
    }
}
